-- 音乐信息数据库表 DDL 语句 (基于接口数据结构) (PostgreSQL版本)

-- 创建主音乐表
CREATE TABLE music (
    id BIGSERIAL PRIMARY KEY,
    rid VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    artist VARCHAR(255) NOT NULL,
    pic VARCHAR(500),
    src VARCHAR(500),
    file_path VARCHAR(500),
    extracted_song_name VARCHAR(255),
    extracted_artist VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE UNIQUE INDEX idx_music_rid ON music(rid);
CREATE UNIQUE INDEX idx_music_file_path ON music(file_path);
CREATE INDEX idx_music_name ON music(name);
CREATE INDEX idx_music_artist ON music(artist);
CREATE INDEX idx_music_extracted_song_name ON music(extracted_song_name);
CREATE INDEX idx_music_extracted_artist ON music(extracted_artist);

-- 创建更新时间自动更新函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 创建触发器
CREATE TRIGGER update_music_updated_at BEFORE UPDATE
ON music FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建下载链接表
CREATE TABLE music_download_url (
    id BIGSERIAL PRIMARY KEY,
    music_id BIGINT NOT NULL,
    url_type VARCHAR(50) NOT NULL,
    url_value VARCHAR(500) NOT NULL,
    formatted_url VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_music_download_url_music_id FOREIGN KEY (music_id) REFERENCES music(id) ON DELETE CASCADE
);

-- 创建下载链接表索引
CREATE INDEX idx_music_download_url_music_id ON music_download_url(music_id);
CREATE INDEX idx_music_download_url_url_type ON music_download_url(url_type);

-- 创建原始数据表(用于存储接口返回的完整JSON数据)
CREATE TABLE music_raw_data (
    id BIGSERIAL PRIMARY KEY,
    rid VARCHAR(50) NOT NULL,
    raw_json TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建原始数据表索引
CREATE UNIQUE INDEX idx_music_raw_data_rid ON music_raw_data(rid);

-- 插入示例数据的SQL语句
-- 示例数据基于你提供的接口返回数据
-- INSERT INTO music (rid, name, artist, pic, src, file_path, extracted_song_name, extracted_artist) VALUES
-- ('17571', 'I Am You', 'kim taylor', 'https://img.qqmp3.vip/image/20240802/20240802095950_7844.webp', 'https://www.qqmp3.vip/api/kw.php?type=mp3&level=exhigh&lrc=true&rid=17571', '/Users/lijun/Library/Mobile Documents/com~apple~CloudDocs/Music/I Am You - Kim Taylor.ncm', 'I Am You', 'Kim Taylor');

-- 插入对应的下载链接数据
-- INSERT INTO music_download_url (music_id, url_type, url_value, formatted_url) VALUES
-- (1, 'WAV下载', 'https://pan.quark.cn/s/b3436796b6c5', 'WAV下载: https://pan.quark.cn/s/b3436796b6c5'),
-- (1, 'MP3下载', 'https://pan.quark.cn/s/d8572f44daae', 'MP3下载: https://pan.quark.cn/s/d8572f44daae');

-- 常用查询示例
-- 1. 查询特定歌曲的所有信息及下载链接
SELECT m.*, md.url_type, md.formatted_url 
FROM music m 
LEFT JOIN music_download_url md ON m.id = md.music_id 
WHERE m.rid = '17571';

-- 2. 查询某个艺术家的所有歌曲
SELECT * FROM music WHERE artist LIKE '%kim taylor%';

-- 3. 查询包含特定关键词的歌曲
SELECT * FROM music WHERE name LIKE '%I Am You%';

-- 4. 查询所有歌曲及其下载链接数量
SELECT m.*, COUNT(md.id) as download_url_count
FROM music m 
LEFT JOIN music_download_url md ON m.id = md.music_id
GROUP BY m.id;

-- 5. 查询特定类型的下载链接
SELECT m.name, m.artist, md.url_type, md.formatted_url
FROM music m 
JOIN music_download_url md ON m.id = md.music_id
WHERE md.url_type = 'MP3下载';